package jesstalk;
import java.sql.*;
import java.util.ArrayList;
import javax.servlet.ServletContext;

/**
 *
 * @author user
 */
public class Db {
    private Connection  connect;
    private Statement   statement, _statement;

    private static final String dbname = "jesstalk";
    private static final String mysqluser = "progin";
    private static final String mysqlpass = "progin";

    public static Db get(ServletContext s) throws ClassNotFoundException, SQLException {
        Db retval = (Db) s.getAttribute("database");
        if(retval == null) {
            retval = new Db();
            s.setAttribute("database", retval);
        }

        return retval;
    }

    public Db() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        connect     = DriverManager.getConnection("jdbc:mysql://localhost/"+dbname+"?user="+mysqluser+"&password="+mysqlpass);
    }

    public ResultSet doQuery(String query) throws SQLException {
        _statement   = connect.createStatement();
        return _statement.executeQuery(query);
    }

    public void doQueryAffectDB(String query) throws SQLException {
        _statement   = connect.createStatement();
        _statement.executeUpdate(query);
    }

    public ArrayList<String> getQuery(String dbn, String query, String column) throws SQLException {
        
            // check database from request
            statement = connect.createStatement();
            //"select * from user where username = 'test3' "
            ResultSet resultSet = statement.executeQuery(query);
            
            ArrayList<String> List = new ArrayList<String>();
            while (resultSet.next()) {
                List.add(resultSet.getString(column));
                //List.add(resultSet.getString("username"));
            }
            return List;
    }

    public void insertInto (String dbn, String query) throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager.getConnection("jdbc:mysql://localhost/"+dbn+"?user="+mysqluser+"&password="+mysqlpass);
            // check database from request
            statement = connect.createStatement();
            //"select * from user where username = 'test3' "
            statement.executeUpdate(query);
    }

    public void updateFrom (String dbn, String query) throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager.getConnection("jdbc:mysql://localhost/"+dbn+"?user="+mysqluser+"&password="+mysqlpass);
            // check database from request
            statement = connect.createStatement();
            //"select * from user where username = 'test3' "
            statement.executeUpdate(query);
    }

    public void deleteFrom (String dbn, String query) throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager.getConnection("jdbc:mysql://localhost/"+dbn+"?user="+mysqluser+"&password="+mysqlpass);
            // check database from request
            statement = connect.createStatement();
            //"select * from user where username = 'test3' "
            statement.executeUpdate(query);
    }
}
